use CreativeSystems;
GO

ALTER PROC pr_GetEventCountPackageType
(
	@DateRangeStart DATETIME,
	@DateRangeEnd DATETIME,
	@EventType VARCHAR(MAX),
	@PackageType VARCHAR(MAX),
	@ClientName VARCHAR(MAX)	
)
AS
	SELECT p.PackageType, COUNT(*) "Count"
	FROM [Event] e
	INNER JOIN Package p ON p.PackageID = e.PackageID
	INNER JOIN EventType et ON et.EventTypeID = e.EventTypeID
	INNER JOIN Client c ON c.ClientID = e.ClientID
	WHERE e.StartDate >= @DateRangeStart
	AND e.StartDate <= @DateRangeEnd
	AND et.EventTypeName = COALESCE(@EventType, et.EventTypeName)
	AND p.PackageType = COALESCE(@PackageType, p.PackageType)
	AND c.FirstName + ' ' + c.LastName = COALESCE(@ClientName, c.FirstName + ' ' + c.LastName)
	Group By p.PackageType;